package org.csu.onlineedubackend.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.extension.plugins.pagination.Page;
import org.apache.ibatis.annotations.Mapper;

import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.csu.onlineedubackend.dto.CourseCommentDTO;
import org.csu.onlineedubackend.entity.CourseComment;

import java.util.List;
import java.util.Map;

/**
 * Mapper interface for CourseComment entity
 */
@Mapper
public interface CourseCommentMapper extends BaseMapper<CourseComment> {

    // 查询某门课程的所有评论
    @Select("""
    SELECT 
        cc.id,
        cc.course_id AS courseId,
        cc.student_id AS studentId,
        u.name AS username,
        u.avatar_url AS userAvatar,
        cc.content,
        cc.stars AS stars,
        cc.created_at AS createdAt,
        cc.created_at AS updatedAt
    FROM course_comment cc
    JOIN user u ON cc.student_id = u.id
    WHERE cc.course_id = #{courseId}
    and cc.status = 0
    ORDER BY cc.created_at DESC
""")
    List<CourseCommentDTO> selectCommentsByCourseId(@Param("courseId") Integer courseId);

    @Update("UPDATE course_comment SET status = 1 WHERE id = #{id}")
    int blockCommentById(Integer id);

    // 查询某教师所有课程的平均评分
    @Select("""
        SELECT AVG(cc.stars)
        FROM course_comment cc
        JOIN course c ON cc.course_id = c.id
        WHERE c.teacher_id = #{teacherId}
          AND cc.status = 0
    """)
    Double getAverageRatingByTeacherId(@Param("teacherId") Integer teacherId);

    // 查询某教师所有课程的评论总数
    @Select("""
        SELECT COUNT(*)
        FROM course_comment cc
        JOIN course c ON cc.course_id = c.id
        WHERE c.teacher_id = #{teacherId}
          AND cc.status = 0
    """)
    int countCommentsByTeacherId(@Param("teacherId") Integer teacherId);

    // 查询某老师所有课程的评论
//    List<CourseCommentDTO> selectCommentsByTeacherId(@Param("teacherId") Integer teacherId);

    // 查询某位课程的评论总数和评价平均分
    @Select({
            "<script>",
            "SELECT COUNT(*) AS commentCount, IFNULL(AVG(stars),0) AS avgStars",
            "FROM course_comment",
            "WHERE status = 0",
            "AND course_id IN",
            "<foreach collection='courseIds' item='id' open='(' separator=',' close=')'>",
            "#{id}",
            "</foreach>",
            "</script>"
    })
    Map<String, Object> getCommentStatsByCourseIds(@Param("courseIds") List<Integer> courseIds);
    /**
     * 根据课程ID查询评论列表
     * 使用明确的字段名称，确保与数据库表结构完全匹配
     * 添加JOIN查询用户表获取用户名和头像
     */
    @Select("SELECT c.id, c.course_id as courseId, c.student_id as studentId, c.content, c.stars, c.like_count as likeCount, " +
           "c.created_at as createdAt, c.status, " +
           "IFNULL(c.username, u.username) as username, IFNULL(c.user_avatar, u.avatar_url) as userAvatar " +
           "FROM course_comment c " +
           "LEFT JOIN user u ON c.student_id = u.id " +
           "WHERE c.course_id = #{courseId} ORDER BY c.created_at DESC")
    IPage<CourseComment> selectCommentsPageByCourseId(Page<CourseComment> page, Integer courseId);
    
    /**
     * 查询课程的平均评分
     * @param courseId 课程ID
     * @return 平均评分
     */
    @Select("SELECT IFNULL(AVG(stars), 0) FROM course_comment WHERE course_id = #{courseId}")
    Double selectAvgRatingByCourseId(Integer courseId);
}